﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;
using System.Web.Configuration;
using System.Text;
using System.IO;

/// <summary>
/// Summary description for cls_UploadFile
/// </summary>
public class UploadHelper
{
    //Initialize mysql connection
    string autoFileId;
    int rowCount = 0;
    MySqlConnection connection;// = new MySqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
    MySqlCommand cmd;
    UserVO objUserVO;

    MySqlDataAdapter adapter;
    DataTable DTItems;

    public UploadHelper()
	{
		//
		// TODO: Add constructor logic here
        connection = new MySqlConnection(WebConfigurationManager.ConnectionStrings["mySQLConnection"].ToString());
		//       
	}

    private void InsertIntoFileUpload(string fileType,string strFilename, string strFileExt)
    {

        string status = "N";//while uploading new always N
        autoFileId = System.DateTime.Now.ToString("ddMMyyHHmmss");
        connection.Open();
        string strQuery = "insert into pro_file_upload_details(customer_id,file_id,file_name, file_type, file_upload_date, file_upload_by," +
            "file_status, error_desc) values " +
            "('"+objUserVO.customerId +"','"+ autoFileId +"','" + strFilename + "','" + fileType + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','" + objUserVO.userId + "','" + status + "','null')";
        cmd = new MySqlCommand(strQuery, connection);
        int retval = cmd.ExecuteNonQuery();
        //If it is -1 show appropriate message

    }

    private int IsFileAlreadyUploaded(string fileType,string strFilename)
    {
        int fileExistOrNot = 0;
        string strQuery = "";
        connection.Open();
        strQuery = "select count(1) from pro_file_upload_details where customer_id='" + objUserVO.customerId + "' and file_type = '" + fileType + "' and file_name = '"+ strFilename +"' and file_status = 'UPLOD'";
        cmd = new MySqlCommand(strQuery, connection);
        fileExistOrNot  = Convert.ToInt32(cmd.ExecuteScalar());
        connection.Close();
        return fileExistOrNot;
    }
    public int ParseUploadedFile(UserVO currentUser, string fileType, string strFilename, string strFileExt,string filePath)
    {
        int retval;
        int isExist = 0;
        objUserVO = currentUser;
        isExist = IsFileAlreadyUploaded(fileType, strFilename);
        if (isExist == 0)
        {
            InsertIntoFileUpload(fileType, strFilename, strFileExt);
            DataTable dtStaggingData = InsertIntoStagging(filePath);
            retval = InsertIntoInvoiceStatement(fileType, dtStaggingData);
        }
        else
        {
            retval = 2;
        }
        return retval;
    }

    private DataTable InsertIntoStagging(string filePath)
    { 
        char strDelimiter = ',';
        DataSet dsReadFile = new DataSet();
        DataTable dtReadFile = new DataTable();                                        

        List<List<String>> rowsList = new List<List<String>>();
        List<String> columnsList = new List<String>();

        using (StreamReader reader = File.OpenText(filePath))
        {
            string line;
            int cnt = 0;
            //  Response.Write("Start at execution" + System.DateTime.Now);
            while ((line = reader.ReadLine()) != null)
            {
                //aaaaaaaaaaaaa,1111111111,bbbbbbbbbbbbbbb,2222222222222222,cccccccccccccc,333333333333333333,dddddddddddd,444444444444                                
                //////columnsList = line.Split(strDelimiter).ToList();
                //////columnsList.Add(Convert.ToString(fileId));

                columnsList = line.Split(strDelimiter).ToList();
                columnsList.Insert(0, (Convert.ToString(autoFileId)));

                if (dtReadFile.Columns.Count <= 0)
                {
                    //columnsList.Count();                                   
                    foreach (String str in columnsList)
                    {
                        dtReadFile.Columns.Add();

                    }
                    //dtReadFile.Columns.Add("FileId");
                }
                //Add row value from list to table row                               
                dtReadFile.Rows.Add(columnsList.ToArray());
                cnt++;
            }
            dsReadFile.Tables.Add(dtReadFile);
            //To add all column of database  to datatable
            while (dtReadFile.Columns.Count != 101)
            {
                dtReadFile.Columns.Add();
            }
            //Take count of newly created file id record in stagging table
            MySqlDataAdapter adp = new MySqlDataAdapter("select * from test_pro_file_staging where column1 = '" + Convert.ToString(autoFileId) + "'", connection);
            DataTable dt = new DataTable();
            adp.Fill(dt);

            // If No records found in the table for this new file id then insert all the rows of the dtReadFile into "pro_file_staging" table
            MySqlCommandBuilder cmdBuilder;
            if (dt != null)
            {
                if (dt.Rows.Count == 0)
                {
                    // Update the DataSet with the Database
                    cmdBuilder = new MySqlCommandBuilder(adp);
                    adp.Update(dtReadFile);//Update database table with file record.

                }
            }
        }
        return dtReadFile;


    }

    private int InsertIntoInvoiceStatement(string fileType,DataTable dtStaggingData)
    {
        //Below code for fetch data from conf_customer_file_format
        try
        {
            string strQuery = "";

            strQuery = "select field_mapping_id from conf_customer_file_format where customer_id='" + objUserVO.customerId + "' and file_type = '" + fileType + "'";
            cmd = new MySqlCommand(strQuery, connection);
            MySqlDataReader drField_mapping_id = cmd.ExecuteReader();
            string field_mapping_id = null;

            while (drField_mapping_id.Read())
            {

                field_mapping_id = field_mapping_id + "'" + drField_mapping_id[0].ToString() + "',";
            }
            drField_mapping_id.Close();
            drField_mapping_id.Dispose();
            field_mapping_id = field_mapping_id.Remove(field_mapping_id.Length - 1, 1);

            //Below code for fetch data from conf_file_field_mapping
            strQuery = "";
            cmd = null;
            strQuery = "select Source_Field_Index,Target_Field_Index from conf_file_field_mapping where field_mapping_id in ( " + field_mapping_id + ")";
            cmd = new MySqlCommand(strQuery, connection);
            MySqlDataReader drSourceTarget = cmd.ExecuteReader();

            DataTable dtField_mapping_id = new DataTable();
            dtField_mapping_id.Load(drSourceTarget);

            String schemaString = "";
            DataTable dtTemp;
            if (fileType == "I")
            {
                dtTemp = getInvoiceDataTable();
                schemaString = "SELECT * FROM pro_invoice where invoice_unique_id = 0";
            }
            else
            {
                dtTemp = getStatementDataTable();
                schemaString = "SELECT * FROM pro_statement where statement_unique_id = 0";
            }
            if (dtField_mapping_id != null && dtField_mapping_id.Rows.Count > 0)
            {
                for (int iRow = 0; iRow < dtStaggingData.Rows.Count; iRow++)
                {
                    DataRow dr = dtTemp.NewRow();
                    for (int i = 0; i < dtField_mapping_id.Rows.Count; i++)
                    {
                        int sourceIndx = Convert.ToInt32(dtField_mapping_id.Rows[i]["Source_Field_Index"]);
                        int targetIndx = Convert.ToInt32(dtField_mapping_id.Rows[i]["Target_Field_Index"]);
                        dr[targetIndx - 1] = dtStaggingData.Rows[iRow]["Column" + sourceIndx];

                    }
                    dtTemp.Rows.Add(dr);
                }
            }

            MySqlDataAdapter adpInvoiceStatement = new MySqlDataAdapter(schemaString, connection);
            MySqlCommandBuilder cmdBuilder;
            adpInvoiceStatement.UpdateBatchSize = 10;

            if (dtTemp != null)
            {
                if (dtTemp.Rows.Count != 0)
                {
                    // Update the DataSet with the Database
                    cmdBuilder = new MySqlCommandBuilder(adpInvoiceStatement);
                    adpInvoiceStatement.Update(dtTemp);//Update database table with file record.
                    rowCount = dtTemp.Rows.Count;
                }
            }

            if (rowCount > 0)
            {
                string strQueryInvoice = "update pro_file_upload_details set file_status = 'UPLOD',row_count = '" + rowCount + "' where customer_id = '" + objUserVO.customerId + "' and file_id = '" + Convert.ToString(autoFileId) + "' and file_type = '" + fileType + "'";
                cmd = new MySqlCommand(strQueryInvoice, connection);
                int retval = cmd.ExecuteNonQuery();
            }
            return rowCount;

        }
        catch (Exception ex)
        {
            return rowCount;
            throw ex;            
        }
        finally
        {
            
        }
    }
    public static UserVO getTestUser()
    {
        UserVO user = new UserVO();
        user.userId = "user1";
        user.customerId = "001";
        return user;
    }
    public static DataTable getInvoiceDataTable()
    {
         DataTable dtStaggingToInvoice = new DataTable();
         dtStaggingToInvoice.Columns.Add("invoice_unique_id", typeof(System.Decimal));
         dtStaggingToInvoice.Columns.Add("remitter_id", typeof(System.String));
         dtStaggingToInvoice.Columns.Add("customer_id", typeof(System.String ));
         dtStaggingToInvoice.Columns.Add("invoice_number", typeof(System.String));
         dtStaggingToInvoice.Columns.Add("date", typeof(System.DateTime));
         dtStaggingToInvoice.Columns.Add("currency", typeof(System.String));
         dtStaggingToInvoice.Columns.Add("amount", typeof(System.String));
         dtStaggingToInvoice.Columns.Add("po_ref", typeof(System.String));
         dtStaggingToInvoice.Columns.Add("service_tax", typeof(System.Int32));
         dtStaggingToInvoice.Columns.Add("vat", typeof(System.Int32));
         dtStaggingToInvoice.Columns.Add("cess", typeof(System.Int32));
         dtStaggingToInvoice.Columns.Add("sales_tax", typeof(System.Int32));
         dtStaggingToInvoice.Columns.Add("tax1", typeof(System.Int32));
         dtStaggingToInvoice.Columns.Add("tax2", typeof(System.Int32));
         dtStaggingToInvoice.Columns.Add("tax3", typeof(System.Int32));
         dtStaggingToInvoice.Columns.Add("discount", typeof(System.Int32));
         dtStaggingToInvoice.Columns.Add("invoic_status", typeof(System.Char));
         dtStaggingToInvoice.Columns.Add("is_partial", typeof(System.Boolean));
         return dtStaggingToInvoice;
    }
  
    public static DataTable getStatementDataTable()
    {
        DataTable dtStaggingToStatement = new DataTable();
        dtStaggingToStatement.Columns.Add("statement_unique_id", typeof(System.Decimal));
        dtStaggingToStatement.Columns.Add("customer_id", typeof(System.String));
        dtStaggingToStatement.Columns.Add("bank_id", typeof(System.String));
        dtStaggingToStatement.Columns.Add("statement_date", typeof(System.DateTime));
        dtStaggingToStatement.Columns.Add("posting_date", typeof(System.DateTime));
        dtStaggingToStatement.Columns.Add("value_date", typeof(System.DateTime));
        dtStaggingToStatement.Columns.Add("tran_type", typeof(System.String));
        dtStaggingToStatement.Columns.Add("narration1", typeof(System.String));
        dtStaggingToStatement.Columns.Add("narration2", typeof(System.String));
        dtStaggingToStatement.Columns.Add("narration3", typeof(System.String));
        dtStaggingToStatement.Columns.Add("narration4", typeof(System.String));
        dtStaggingToStatement.Columns.Add("amount", typeof(System.Double));
        dtStaggingToStatement.Columns.Add("ref_no_or_cheque_no", typeof(System.String));
        dtStaggingToStatement.Columns.Add("reconciled_amount", typeof(System.Double));
        dtStaggingToStatement.Columns.Add("statement_status", typeof(System.Char));
        dtStaggingToStatement.Columns.Add("is_partial", typeof(System.Boolean));
        return dtStaggingToStatement;
    }

}